package com.altocloud.parse;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

import com.altocloud.database.DbUtil;
import com.altocloud.level3.entities.StationDetailsExtra;
import com.altocloud.level3.entities.Network;
import com.altocloud.level3.entities.Provider;
import com.altocloud.level3.entities.StationDetails;
import com.altocloud.level3.entities.Weather;

public class Level3 {

	double parseDoubleValue(String str) {
		Double temp = 0.0;
		try {
			temp = Double.parseDouble(str);
		} catch (NumberFormatException E) {
			// E.printStackTrace();
			temp = -1.0;
		}
		return temp;
	}

	int parseIntValue(String str) {
		int temp = 0;
		try {
			temp = Integer.parseInt(str);
		} catch (Exception E) {
			// E.printStackTrace();
			temp = 0;
		}
		return temp;
	}

	public static void main(String[] args) {
		double avg = 0.0;

	}

	DbUtil db = new DbUtil();
	Connection conn = db.getDbConnection("226-Level-3");
	Statement stmt = null;
	ResultSet rs = null;
	String sql = "";
	int count1;
	PreparedStatement pst = null;
	long id = 1;

	public void parseCsvFile(String path) {
		try {
			FileInputStream fstream = new FileInputStream(path);

			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));

			String strLine = "";
			String data[] = new String[22];
			// int count=0;
			Network network = new Network();
			Provider provider = new Provider();
			StationDetails stndetail = new StationDetails();
			StationDetailsExtra stnextra = new StationDetailsExtra();

			stmt = conn.createStatement();

			while ((strLine = br.readLine()) != null) {
				data = strLine.split(",");

				// Network Details
				network.setNetworkId(parseIntValue(data[8]));
				network.setNetworkName(data[9]);

				// Provider Details
				provider.setProviderId(parseIntValue(data[11]));
				provider.setProviderName(data[12]);

				// Station Details
				stndetail.setStationCode(data[0]);
				stndetail.setStationName(data[2]);
				stndetail.setState(data[3]);
				stndetail.setCountry(data[4]);
				stndetail.setSecondary_id(data[1]);
				stndetail.setMnet(parseIntValue(data[8]));

				double roundlat = (Math
						.round(parseDoubleValue(data[5]) * 100.0) / 100.0);
				stndetail.setLatitude(roundlat);

				double roundlon = (Math
						.round(parseDoubleValue(data[6]) * 100.0) / 100.0);
				stndetail.setLongitude(roundlon);

				double roundel = (Math.round(parseDoubleValue(data[7]) * 100.0) / 100.0);
				stndetail.setElevation(roundel);

				// Station Details Extra
				stnextra.setStationCode(data[0]);
				stnextra.setStationName(data[2]);
				stnextra.setStatus(data[10]);
				stnextra.setPrimaryProviderId(parseIntValue(data[11]));
				stnextra.setSecondaryProviderId(parseIntValue(data[13]));
				stnextra.setTertiaryProvideId(parseIntValue(data[15]));

				// To insert into network_details table
				try {
					sql = "select count(*) from network_details where network_id="
							+ network.getNetworkId() + "";
					rs = stmt.executeQuery(sql);
					rs.next();
					count1 = rs.getInt(1);
					if (count1 == 0) {

						sql = "insert into network_details"
								+ "(network_id, network_name) values" + "(?,?)";
						pst = conn.prepareStatement(sql);
						pst.setInt(1, network.getNetworkId());
						pst.setString(2, network.getNetworkName());
						pst.executeUpdate();

					}

					else {
						sql = "update network_details set network_name='"
								+ network.getNetworkName()
								+ "' where network_id="
								+ network.getNetworkId();

						stmt.executeUpdate(sql);
					}
				} catch (Exception e) {

				}

				// To insert into provider_details table
				sql = "select count(*) from provider_details where provider_id="
						+ provider.getProviderId() + "";
				rs = stmt.executeQuery(sql);
				rs.next();
				count1 = rs.getInt(1);
				if (count1 == 0) {
					sql = "insert into provider_details"
							+ "(provider_id, provider_name) values" + "(?,?)";
					pst = conn.prepareStatement(sql);
					pst.setInt(1, provider.getProviderId());
					pst.setString(2, provider.getProviderName());
					pst.executeUpdate();
				}

				else {
					sql = "update provider_details set provider_name='"
							+ provider.getProviderName()
							+ "' where provider_id=" + provider.getProviderId();

					stmt.executeUpdate(sql);

				}

				// To insert into station_details table
				try {
					sql = "select count(*) from station_details where stn='"
							+ stndetail.getStationCode() + "' and"
							+ " stn_name='" + stndetail.getStationName() + "'";
					rs = stmt.executeQuery(sql);
					// System.out.println(sql);
					rs.next();
					count1 = rs.getInt(1);
					if (count1 == 0) {

						sql = "insert into station_details"
								+ "(stn,stn_name,country,elev,lat,lon,secondary_id,state,networkdetails_network_id) values"
								+ "(?,?,?,?,?,?,?,?,?)";
						// System.out.println(sql);
						pst = conn.prepareStatement(sql);
						pst.setString(1, stndetail.getStationCode());
						pst.setString(2, stndetail.getStationName());
						pst.setString(3, stndetail.getCountry());
						pst.setDouble(4, stndetail.getElevation());
						pst.setDouble(5, stndetail.getLatitude());
						pst.setDouble(6, stndetail.getLongitude());
						pst.setString(7, stndetail.getSecondary_id());
						pst.setString(8, stndetail.getState());
						pst.setInt(9, stndetail.getMnet());
						pst.executeUpdate();

					}

					else {

						sql = "update station_details set country='"
								+ stndetail.getCountry() + "', elev="
								+ stndetail.getElevation() + ", lat="
								+ stndetail.getLatitude() + ", lon="
								+ stndetail.getLongitude() + ", secondary_id="
								+ stndetail.getSecondary_id() + ", state='"
								+ stndetail.getState()
								+ "',networkdetails_network_id="
								+ stndetail.getMnet() + " where stn='"
								+ stndetail.getStationCode()
								+ "' and stn_name='"
								+ stndetail.getStationName();

						// System.out.println(sql);

						stmt.executeUpdate(sql);

					}
				} catch (Exception e) {

				}

				// To insert into station_details_extra table
				try {
					sql = "select count(*) from stn_details_extra where stn_details_stn='"
							+ stnextra.getStationCode()
							+ "' and"
							+ " stn_details_stn_name='"
							+ stnextra.getStationName() + "'";
					rs = stmt.executeQuery(sql);
					rs.next();
					count1 = rs.getInt(1);
					if (count1 == 0) {
						// System.out.println("in stn extra");
						sql = "insert into stn_details_extra"
								+ "(status,stn_details_stn,stn_details_stn_name,priprodetails_provider_id, secprodetails_provider_id, terprodetails_provider_id) values"
								+ "(?,?,?,?,?,?)";
						pst = conn.prepareStatement(sql);
						pst.setString(1, stnextra.getStatus());
						pst.setString(2, stnextra.getStationCode());
						pst.setString(3, stnextra.getStationName());
						pst.setInt(4, stnextra.getPrimaryProviderId());
						pst.setInt(5, stnextra.getSecondaryProviderId());
						pst.setInt(6, stnextra.getTertiaryProviderId());
						pst.executeUpdate();

					}

					else {
						continue;
						// sql="update network_details set network_name='" +
						// network.getNetworkName() +
						// "' where network_id=" + network.getNetworkId();

						// stmt.executeUpdate(sql);
					}
				} catch (Exception e) {

				}

			}
			System.out.println("Details stored");
			// Close the input stream
			in.close();
		} catch (Exception e) {
			System.out.println("Exception: " + e);
			e.printStackTrace();
		}

	}

	public void parseOutFile(String path) {
		try {
			FileInputStream fstream = new FileInputStream(path);

			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));

			String strLine;
			String pattern = "\\s{1,}";

			// Read File Line By Line
			for (int i = 0; i < 4; i++) {
				strLine = br.readLine();
			}

			// Read File Line By Line
			String data[] = new String[22];
			String stationName = "";
			stmt = conn.createStatement();
			while ((strLine = br.readLine()) != null) {
				stationName = "";
				strLine = strLine.replaceAll(pattern, "|");
				// System.out.println("" + strLine);
				data = strLine.split("\\|");
				// System.out.println("hUnGrY ReNiSh" + data.length);
				// PARM =
				// MNET;SLAT;SLON;SELV;TMPF;SKNT;DRCT;GUST;PMSL;ALTI;DWPF;RELH;WTHR;P24I
				// |BULLF|20121003/0215|8.00|37.52|-110.73|1128.00|78.70|2.74|264.20|3.39|-9999.00|-9999.00|46.41|32.09|-9999.00|-9999.00

				if (data.length == 17) {
					// Level-3 Weather Domain
					Weather wthr = new Weather();

					// Weather Details
					wthr.setId(id++);
					wthr.setStnId(data[1]);
					// System.out.println("id = " + data[1]);
					wthr.setALTI(Double.parseDouble(data[11]));
					wthr.setDRCT(Double.parseDouble(data[8]));
					wthr.setDWPF(Double.parseDouble(data[12]));
					wthr.setGUST(Double.parseDouble(data[9]));
					wthr.setP24I(Double.parseDouble(data[15]));
					wthr.setPMSI(Double.parseDouble(data[10]));
					wthr.setRELH(Double.parseDouble(data[13]));
					wthr.setSKNT(Double.parseDouble(data[7]));
					wthr.setTMPF(Double.parseDouble(data[6]));
					wthr.setWTHR(Double.parseDouble(data[14]));

					final String INPUT1 = "yyyyMMdd/HHmm";
					final String OUTPUT1 = "yyyy-MM-dd HH.mm";
					String in1 = data[2];
					java.util.Date date = (new SimpleDateFormat(INPUT1))
							.parse(in1);
					Timestamp t = new Timestamp(date.getTime());
					wthr.setDate(t);

					try {

						// System.out.println("inside weather");
						sql = "insert into weather"
								+ "(alti, drct, dwpf, gust, p24i, pmsl, relh, sknt, tmpf, wthr, date, stn_id_stn) values "
								+ "(?,?,?,?,?,?,?,?,?,?,?,?)";

						// System.out.println("sql is: "+sql);

						pst = conn.prepareStatement(sql);
						// pst.setLong(1,wthr.getId());
						pst.setDouble(1, wthr.getALTI());
						pst.setDouble(2, wthr.getDRCT());
						pst.setDouble(3, wthr.getDWPF());
						pst.setDouble(4, wthr.getGUST());
						pst.setDouble(5, wthr.getP24I());
						pst.setDouble(6, wthr.getPMSI());
						pst.setDouble(7, wthr.getRELH());
						pst.setDouble(8, wthr.getSKNT());
						pst.setDouble(9, wthr.getTMPF());
						pst.setDouble(10, wthr.getWTHR());
						pst.setTimestamp(11, wthr.getDate());
						pst.setString(12, wthr.getStnId());
						// pst.setString(14, wthr.getStnName());
						pst.executeUpdate();
					} catch (Exception e) {
						System.out.println("exception in sql insert");
						e.printStackTrace();
					}
					// System.out.println("Station Extra details stored");

				}
			}
			System.out.println("weather details stored");
			// Close the input stream

			in.close();
		} catch (Exception e) {

			System.out.println("Exception: " + e);
			e.printStackTrace();
		}

	}

}
